/**
 * Copyright 2016-2017 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.inmorn.extspring.jdbc;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.inmorn.extspring.exception.DaoException;
import com.inmorn.extspring.jdbc.annotation.Column;
import com.inmorn.extspring.jdbc.annotation.Table;
import com.inmorn.extspring.util.StringUtils;

/**
 * JDBC数据操作实现,注入dataSource
 * 
 * @author Jeff.Li
 * 
 */
public class BaseJdbcDao extends JdbcDaoSupport implements IJdbcDao {
	/**
	 * 日志工具类, 使用如:
	 * 
	 * <pre>
	 * if (logger.isDebugEnabled())
	 * 	logger.debug(&quot;error id : {0}&quot;, id);
	 * </pre>
	 */
	protected Logger logger = LoggerFactory.getLogger(getClass());
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Object queryObject(String sql,Object[] args,Class<?> clazz){
		List<?> list =  getJdbcTemplate().query(
				sql,args,new BeanPropertyRowMapper(clazz));
		if(list == null || list.size() <= 0) return null;
		return list.get(0);
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public <T> T queryObjectNew(String sql,Object[] args,Class<T> clazz){
		List<T> list =  getJdbcTemplate().query(
				sql,args,new BeanPropertyRowMapper(clazz));
		if(list == null || list.size() <= 0) return null;
		return list.get(0);
	}

	protected void appendSql(StringBuffer sub,List<Object> args,String sql,String arg){
		if(sub == null || args == null || StringUtils.isEmpty(sql)){
			throw new RuntimeException("StringBuffer sub is null or List<Object> args is null or sql is null");
		}
		
		if(StringUtils.isNotEmpty(arg)){
			sub.append(sql);
			args.add(arg);
		}
	}

	// 实现SQL查询
	public List<Map<String, Object>> query(String sql, List<Object> args)
			throws DaoException {
		return (List<Map<String, Object>>) getJdbcTemplate().query(sql,
				args == null ? new Object[0] : args.toArray(),
				new ColumnMapRowMapper());
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<?> query(String sql, List<Object> args,RowMapper rowMapper)
			throws DaoException {
		return getJdbcTemplate().query(sql,
				args == null ? new Object[0] : args.toArray(),rowMapper);
	}
	
	
	
	/**
	 * @param sql
	 * @param args
	 * @param rowMapper
	 * @return
	 * @throws DaoException
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<?> query(String sql, List<Object> args, RowMapper rowMapper, int queryTimeout)
			throws DaoException {
		getJdbcTemplate().setQueryTimeout(queryTimeout);
		return getJdbcTemplate().query(sql,
				args == null ? new Object[0] : args.toArray(), rowMapper);
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public void call(final String procedureName, final List<Object> args)
			throws DaoException {
		getJdbcTemplate().execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(
					Connection connection) throws SQLException {
				CallableStatement stmt = connection.prepareCall(procedureName);
				if (args != null && args.size() > 0) {
					for (int i = 0, n = args.size(); i < n; i++) {
						stmt.setObject(i, args.get(i));
					}
				}
				return stmt;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement stmt)
					throws SQLException, DataAccessException {
				stmt.execute();
				return null;
			}
		});
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<Map<String, Object>> callQuery(final String procedureName,
			final List<Object> args) throws DaoException {
		return (List<Map<String, Object>>) getJdbcTemplate().execute(
				new CallableStatementCreator() {
					public CallableStatement createCallableStatement(
							Connection connection) throws SQLException {
						CallableStatement stmt = connection
								.prepareCall(procedureName);
						if (args != null && args.size() > 0) {
							for (int i = 0, n = args.size(); i < n; i++) {
								stmt.setObject(i, args.get(i));
							}
						}
						return stmt;
					}
				}, new CallableStatementCallback() {
					public Object doInCallableStatement(CallableStatement stmt)
							throws SQLException, DataAccessException {
						List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
						ResultSet rs = stmt.executeQuery();
						ResultSetMetaData meta = rs.getMetaData();
						int col = meta.getColumnCount();
						while (rs.next()) {
							Map<String, Object> map = new HashMap<String, Object>();
							for (int i = 0; i < col; i++) {
								map
										.put(meta.getCatalogName(i), rs
												.getObject(i));
							}
							results.add(map);
						}
						return results;
					}
				});
	}
	
	/**
	 * 批量操作(添加、更新、删除)
	 * 一个sql参数对应一个List<Object>，如：insert into table values(?,?,?)中有
	 * 3个参数，则需传3个List<Object>参数，List<Object>元素的个数与批量操作的记录数一致
	 * @param sql:带参数的sql语句
	 * @param objects:可变参数，可变类型为Object[]数组，一个sql参数对应一个Object[]数组
	 * @return
	 */
	public int[] batchUpdate(String sql, List<Object>... args){
		final List<Object>[] objects = args;
		// 参数不允许为空
		if (objects == null) {
			return null;
		}
		// 每个参数对应的集合不能为空
		if (objects != null) {
			for (int i = 0; i < objects.length; i++) {
				if (objects[i] == null || objects[i].isEmpty()) {
					return null;
				}
			}
			// 如果有2个以上的参数, 则每个参数的元素个数应该一致
			if (objects.length > 1) {
				for (int i = 0; i < objects.length -1; i++) {
					if (objects[i].size() != objects[i+1].size()) {
						return null;
					}
				}
			}
		}
		BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
			
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				for (int j = 0; j < objects.length; j++) {
					ps.setObject(j + 1, objects[j].get(i));
				}
			}
			
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return objects[0].size();
			}
		};
		return this.getJdbcTemplate().batchUpdate(sql, setter);
	}
	
	protected static Map<Class<?>, String> classTableName = new HashMap<Class<?>, String>();
	protected static Map<Class<?>, Field[]> classFields = new HashMap<Class<?>, Field[]>();
	
	public void validateClazz(Class<?> clazz){
		if(clazz == null){
			throw new RuntimeException("Class is null");
		}
		if(clazz.getAnnotation(Table.class) == null){
			throw new RuntimeException("Class:" + clazz + ",not found @Table ");
		}
	}
	
	public String getTableName(Class<?> clazz){
		String tableName = classTableName.get(clazz);
		if(StringUtils.isEmpty(tableName)){
			tableName = clazz.getAnnotation(Table.class).value();
			classTableName.put(clazz, tableName);
		}
		return tableName;
	}
	
	public Field[] getClassFields(Class<?> clazz){
		Field[] fields = classFields.get(clazz);
		if(fields == null){
			fields = clazz.getDeclaredFields();
			classFields.put(clazz, fields);
		}
		return fields;
	}
	
	/**
	 * 返回单条记录
	 * @param clazz
	 * @param columnAndValue
	 * @param appendSql
	 * @param appendArgs
	 * @return
	 */
	public <T> T queryForObject(Class<?> clazz,Map<String, Object> columnAndValue,
			String appendSql,Object[] appendArgs){
		List<T> list = queryForList(clazz, columnAndValue, appendSql, appendArgs);
		if(list != null && list.size() > 0){
			return list.get(0);
		}
		return null;
	}
	
	/**
	 * 根据实体类Class查询List
	 * @param clazz 实体类Class
	 * @param columnAndValue 列名和参数
	 * @param appendSql 需要额外添加的Sql
	 * @param appendArgs 额外参数
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public <T> List<T> queryForList(Class<?> clazz,Map<String, Object> columnAndValue,
			String appendSql,Object[] appendArgs){
		validateClazz(clazz);
		
		StringBuffer sqlString = new StringBuffer(" select  ");
		Field[] fields = getClassFields(clazz);
		boolean existColumn = false;
		for (Field field : fields) {
			Column column = field.getAnnotation(Column.class);
			if(column != null){
				sqlString.append("t." + column.value() + ",");
				existColumn = true;
			}
		}
		
		if(!existColumn){
			sqlString.append(" * ");
		}else{
			sqlString.deleteCharAt(sqlString.length() - 1);
		}
		sqlString.append(" from ").append(getTableName(clazz)).append(" t ");
		
		List<Object> args = new ArrayList<Object>();
		if(columnAndValue != null && columnAndValue.size() > 0){
			int i = 0;
			for(Entry<String, Object> entry : columnAndValue.entrySet()){
				sqlString.append(i == 0 ? " where t." : " and t.").append(entry.getKey());
				sqlString.append(" = ? ");
				args.add(entry.getValue());
				i++;
			}
		}
		if(StringUtils.isNotEmpty(appendSql)){
			sqlString.append(appendSql);
		}
		
		if(appendArgs != null && appendArgs.length > 0){
			for(Object valueObj : appendArgs){
				args.add(valueObj);
			}
		}
		return getJdbcTemplate().query(sqlString.toString(), args.toArray(), new BeanPropertyRowMapper(clazz));
	}
	
	/**
	 * 根据实体类Class查询count
	 * @param clazz 实体类Class
	 * @param columnAndValue 列名和参数
	 * @param appendSql 需要额外添加的Sql
	 * @param appendArgs 额外参数
	 * @return
	 */
	public int queryCount(Class<?> clazz,Map<String, Object> columnAndValue,
			String appendSql,Object[] appendArgs){
		validateClazz(clazz);
		
		StringBuffer sqlString = new StringBuffer(" select count(*) from ");
		sqlString.append(getTableName(clazz)).append(" t ");
		
		List<Object> args = new ArrayList<Object>();
		if(columnAndValue != null && columnAndValue.size() > 0){
			int i = 0;
			for(Entry<String, Object> entry : columnAndValue.entrySet()){
				sqlString.append(i == 0 ? " where t." : " and t.").append(entry.getKey());
				sqlString.append(" = ? ");
				args.add(entry.getValue());
				i++;
			}
		}
		if(StringUtils.isNotEmpty(appendSql)){
			sqlString.append(appendSql);
		}
		
		if(appendArgs != null && appendArgs.length > 0){
			for(Object valueObj : appendArgs){
				args.add(valueObj);
			}
		}
		return getJdbcTemplate().queryForObject(sqlString.toString(), args.toArray(), Integer.class);
	}
	
	/**
	 * 根据实体类新增记录,实体类必须要有@Table和属性有@Column
	 * @param entity
	 * @return
	 */
	public int insert(Object entity){
		Field[] fields = getClassFields(entity.getClass());
		if(fields == null){
			throw new RuntimeException("Class:"+entity.getClass()+",Fields is null ");
		}
		
		StringBuffer insertSql = new StringBuffer(" insert into ").append(getTableName(entity.getClass())).append("(");
		List<Object> args = new ArrayList<Object>();
		boolean existColumn = false;
		int columnCount = 0;
		for (Field field : fields) {
			Column column = field.getAnnotation(Column.class);
			if(column != null){
				Object value = null;
				try {
					Method method = entity.getClass().getMethod(
							"get"+StringUtils.firstToUpperCase(field.getName()));
					value = method.invoke(entity);
				} catch (Exception e) {
					e.printStackTrace();
				}
				if(value != null){
					insertSql.append(column.value()).append(",");
					args.add(value);
					existColumn = true;
					columnCount ++;
				}
			}
		}
		
		if(!existColumn){
			throw new RuntimeException(" insert into " + entity.getClass() + " fields not @Column");
		}
		
		insertSql.deleteCharAt(insertSql.length() - 1);
		insertSql.append(" ) values( ");
		for(int i = 0;i<columnCount;i++)
			insertSql.append("?,");
		
		insertSql.deleteCharAt(insertSql.length() - 1);
		insertSql.append(" ) ");
		return getJdbcTemplate().update(insertSql.toString(),args.toArray());
	}
	
	/**
	 * 更新表记录
	 * @param clazz
	 * @param updateColumnAndValue 更新列和值
	 * @param whereColumnAndValue  where条件列和值
	 * @return
	 */
	public int update(Class<?> clazz,Map<String, Object> updateColumnAndValue,
			Map<String, Object> whereColumnAndValue){
		StringBuffer updateSql = new StringBuffer(" update ").append(getTableName(clazz)).append(" set ");
		List<Object> args = new ArrayList<Object>();
		for(Entry<String, Object> entry : updateColumnAndValue.entrySet()){
			updateSql.append(entry.getKey()).append(" = ?,");
			args.add(entry.getValue());
		}
		updateSql.deleteCharAt(updateSql.length() - 1);
		int i = 0;
		for(Entry<String, Object> entry : whereColumnAndValue.entrySet()){
			updateSql.append(i == 0 ? " where " : " and ").append(entry.getKey()).append(" = ? ");
			args.add(entry.getValue());
			i++;
		}
		return getJdbcTemplate().update(updateSql.toString(),args.toArray());
	}
	
}
